package DBLayer;
import ModelLayer.*;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class DBSalesProducts implements IFDBSalesProducts{
	private Connection con;
	
	public DBSalesProducts() {
        con = DBConnection.getInstance().getDBcon();
      }
	public ArrayList<SalesProducts> getOrderLines(int saleID, boolean retriveAssociation)
    {
		String wClause = " saleID = " + saleID;
		return miscWhere(wClause, retriveAssociation);
    }
	public void addSalesProduct(SalesProducts salesProduct){
	   String query="INSERT INTO SalesProducts  VALUES(" +
			salesProduct.getSaleID()+ ", "+ salesProduct.getProduct().getProductID() + "," + salesProduct.getQuantity()+")";
      
      System.out.println("insert : " + query);		  					
      try{
    	  con.setAutoCommit(false);
          Statement stmt = con.createStatement();
	 	  stmt.setQueryTimeout(5);
	 	  stmt.executeUpdate(query);
	 	  stmt.close();
	 	  con.commit();
	 	  con.setAutoCommit(true);
		}	
	 	catch(SQLException ex){
	 	  	System.out.println("Insert exception in employee db: "+ex);
	 	  	try{
	 	  		con.rollback();
	 	  		con.setAutoCommit(true);
	 	  		System.out.println("Rolled back");
	 	  	}catch(Exception e1){
	 	  		System.out.println("Error in rolling back: "+e1);
	 	  	}
	   }
	}
	public void updateProduct(Product product, int quantity, int saleID) throws Exception {
		String query="UPDATE SalesProducts SET " +
				"quantity = "+quantity+", "+
				"saleID = "+saleID+" "+
				"WHERE productID = "+product.getProductID();
                System.out.println("Update query:" + query);
  		try{ 
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	stmt.executeUpdate(query);
                      
	 	 	stmt.close();
		}
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in sales products db: "+ex);
	  	}
	}
	public void deleteProduct(int productID) throws Exception {
		String query="DELETE FROM SalesProducts WHERE productID = " +productID+ " and saleID = null";
                System.out.println(query);
	  	try{ // delete from product
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut try	
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in sales products db: "+ex);
   	        }
	}
	private ArrayList<SalesProducts> miscWhere(String wClause, boolean retrieveAssociation){
		ResultSet results;
		ArrayList<SalesProducts> list = new ArrayList<SalesProducts>();
		String query =  buildQuery(wClause);
	  
		try{ 
			
		 	Statement stmt = con.createStatement();
		 	stmt.setQueryTimeout(5);
		 	results = stmt.executeQuery(query);
		 		
			while(results.next() ){
			     SalesProducts salesProductObj = new SalesProducts();
			     salesProductObj = buildSalesProducts(results);	
	             list.add(salesProductObj);	
				}
			if(retrieveAssociation){
				IFDBProduct dbProduct = new DBProduct();
                for(SalesProducts productObj : list){
                   int productID = productObj.getProduct().getProductID();;
                   Product prodObj = dbProduct.findProduct(productID);
                   productObj.setProduct(prodObj);
                   
                	}
				stmt.close();
			}
			}
		 	catch(Exception e){
		 		System.out.println("Query exception - select: "+e);
				e.printStackTrace();
		 	}
			return list;
	}
	private String buildQuery(String wClause)
	{
	    String query="SELECT * FROM SalesProducts ";
		
		if (wClause.length()>0)
			query= query+"WHERE "+ wClause;
			
		return query;
	}
	private SalesProducts buildSalesProducts(ResultSet results)
    {   
		SalesProducts salesProducts = new SalesProducts();
        try{
        	salesProducts.setSaleID(results.getInt(1));
        	salesProducts.setSaleID(results.getInt(2));
        	salesProducts.setSaleID(results.getInt(3));
        }
       catch(Exception e)
       {
           System.out.println("error in building the employee object");
       }
       return salesProducts;
    }
}